package com.candy.databasedocuments.service.database;

import com.candy.databasedocuments.entity.DatabaseConfig;
import com.candy.databasedocuments.entity.Table;
import com.candy.databasedocuments.entity.TableFieldInfo;
import com.candy.databasedocuments.entity.TableKeyInfo;
import com.candy.databasedocuments.service.AbstractDatabaseConnect;
import com.candy.databasedocuments.utils.JdbcConnectUtil;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author suYanLong
 * @version 1.0
 * @date 2023/7/5 14:44
 * @Description mysql链接处理
 */
@Service("mysql")
public class MysqlDatabaseConnect extends AbstractDatabaseConnect {

    @Resource
    MysqlTableMateSqlServiceImpl tableMateSqlService;

    @Override
    public void getConnectInfo(DatabaseConfig databaseConfig) {
        String url = "jdbc:mysql://" + databaseConfig.getDbAttr().getUrl() + databaseConfig.getDbAttr().getDbName() + "?serverTimezone=GMT%2B8&allowMultiQueries=true";
        databaseConfig.getDbAttr().setUrl(url);
    }

    @Override
    public List<Table> getTableBaseInfoList(Connection connection) {
        //获取数据库名称
        String dateMateName = getDateMateName(connection);

        //获取数据库表名和备注
        List<Table> list = getDateMateTableName(connection, dateMateName);

        //获取表详细信息
        list = selectTablesFields(connection, list);
        list = selectTablesKeys(connection, list);

        return list;
    }

    @Override
    public String getDateMateName(Connection connection) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String dateMateName = null;
        try {
            //执行sql
            String dateMateNameSql = tableMateSqlService.getDateMateName();
            //获取数据库操作对象
            statement = connection.prepareStatement(dateMateNameSql);

            resultSet = statement.executeQuery(dateMateNameSql);

            //处理查询结果集
            while (resultSet.next()) {
                //jdbc 中从1 开始
                dateMateName = resultSet.getString(1);
            }

        } catch (Exception e) {
            System.out.println("数据库异常--获取数据库名称");
            e.printStackTrace();
        }
        return dateMateName;
    }

    @Override
    public List<Table> getDateMateTableName(Connection connection, String dateMateName) {
        List<Table> list = new ArrayList<>();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {

            String dateMateTableNameSql = tableMateSqlService.getDateMateTableName();

            statement = connection.prepareStatement(dateMateTableNameSql);

            statement.setString(1, dateMateName);

            resultSet = statement.executeQuery();

            //处理查询结果集
            String tableName = null;
            String tableComment = null;
            Table table = null;

            while (resultSet.next()) {
                //jdbc 中从1 开始
                tableName = resultSet.getString(1);
                tableComment = resultSet.getString(2);
                table = new Table(tableName, tableComment);
                list.add(table);
            }
        } catch (Exception e) {
            System.out.println("数据库异常--获取数据库表名");
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Table> selectTablesFields(Connection connection, List<Table> tables) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String sql = tableMateSqlService.selectTables(tables.stream().map(Table::getTableName).collect(Collectors.toList()));

            statement = connection.prepareStatement(sql);

            for (int i = 0; i < tables.size(); i++) {
                int j = i + 1;
                statement.setString(j, tables.get(i).getTableName());
            }
            statement.setString(tables.size() + 1, super.databaseConfig.getDbAttr().getDbName());

            Map<String, List<TableFieldInfo>> tableFieldMap = new HashMap<>();

            resultSet = statement.executeQuery();
            while (resultSet.next()) {

                String tableName = resultSet.getString(1);
                List<TableFieldInfo> tableFieldInfos = tableFieldMap.get(tableName);
                if (CollectionUtils.isEmpty(tableFieldInfos)) {
                    tableFieldInfos = new ArrayList<>();
                }
                tableFieldInfos.add(new TableFieldInfo(resultSet.getString(2), resultSet.getString(4), resultSet.getString(5),
                        resultSet.getString(6), resultSet.getString(7), resultSet.getString(8), resultSet.getString(3)));
                tableFieldMap.put(tableName, tableFieldInfos);
            }
            tables.stream().forEach(table -> table.setFields(tableFieldMap.get(table.getTableName())));
        } catch (Exception e) {
            System.out.println("数据库异常--获取表详细信息");
            e.printStackTrace();
        } finally {
            // JdbcConnectUtil.closeResources(connection, statement, resultSet);
        }
        return tables;
    }

    @Override
    public List<Table> selectTablesKeys(Connection connection, List<Table> tableNames) {

        tableNames.forEach(table -> {
            String sql = tableMateSqlService.selectKeys(table.getTableName());
            try {
                PreparedStatement statement = connection.prepareStatement(sql);
                ResultSet resultSet = statement.executeQuery();
                Map<String, TableKeyInfo> keyInfoMap = new HashMap<>();

                while (resultSet.next()) {
                    String indexName = resultSet.getString(3);
                    TableKeyInfo tableKeyInfo = keyInfoMap.get(indexName);
                    if (tableKeyInfo == null) {
                        List<String> columns = new ArrayList<>();
                        columns.add(resultSet.getString(5));
                        tableKeyInfo = new TableKeyInfo(indexName, columns, "0".equals(resultSet.getString(2)), resultSet.getString(11), resultSet.getString(13));
                    } else {
                        tableKeyInfo.getColumns().add(resultSet.getString(5));
                    }
                    keyInfoMap.put(indexName, tableKeyInfo);
                }
                table.setKeys(new ArrayList<TableKeyInfo>(keyInfoMap.values()));
                JdbcConnectUtil.closeResources(null, statement, resultSet);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        });

        JdbcConnectUtil.closeResources(connection, null, null);
        return super.selectTablesKeys(connection, tableNames);
    }

}
